Imports System
Imports System.Data
Imports System.Collections
Imports System.Reflection
Imports System.Activator
Imports System.Type
Imports System.Web
Imports System.Collections.Generic

''' <summary>
''' Dynamic Collections and query execution support.
''' </summary>
''' <remarks>
''' This class is designed to be overridden by the 
''' matching DataObject client class Collection to 
''' maintain the specific 'where' clauses which are
''' extended methods on the object - which call down
''' to the find passing simply a 'where' clause to
''' perform the find filter.
''' 
''' Must dispose this object when finished with it.
''' </remarks>
Public MustInherit Class DOCollection
    Inherits System.MarshalByRefObject
    Implements IDisposable
    Implements IEnumerable

    'object handles
    Protected m_objDA As DataAccess
    Private m_objFactory As DOFactory
    Private m_tableName As String
    Private m_UidColumnName As String
    Private m_cache As Hashtable
    Private m_uidList As DataSet = Nothing
    Private m_resultSet As DataSet = Nothing
    Private m_active As Boolean = False
    Private m_sqlObj As SQLBuilder = Nothing

    ''' <summary>
    ''' The state of the current collection; Has it had a Find() or Fetch() invoked?
    ''' </summary>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public ReadOnly Property Active() As Boolean
        Get
            Return m_active
        End Get
    End Property


    ''' <summary>
    ''' Constructor
    ''' </summary>
    ''' <param name="factory">A reference to the (matching) final DataObject class factory for this DOCollection
    ''' </param>
    ''' <remarks>
    ''' </remarks>
    Protected Sub New(ByVal factory As DOFactory)
        m_objDA = New DataAccess
        m_objFactory = factory
        Dim d As DataObject = m_objFactory.Create()
        m_tableName = d.TableName()
        m_UidColumnName = d.UIDColumnName()
    End Sub

#Region "Deprecated Methods"

    ''' <summary>
    ''' Performs a find which populates the collection.
    ''' </summary>
    ''' <param name="sqlObj">Populated SQLBuilder</param>
    ''' <remarks>
    ''' Performing a find will not load the actual DataObjects. 
    ''' Instead, caching of actual data objects will occur on Item() access
    ''' or any method which acesses the items in the collection.
    ''' </remarks>
    ''' <example>
    ''' <code>
    '''     Dim sColl as New StudentCollection
    '''     Dim mySql as SQLBuilder = sColl.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     sColl.Find(mySql)
    '''     For i as Integer = 0 to sColl.Length-1
    '''         Dim sObj as Student=sColl(i)
    '''         ....
    '''     Next
    ''' </code>
    ''' </example>     
    <ObsoleteAttribute("The CurrentQuery should be set without passing in a QueryBuilder on the Find method.")> _
    Protected Sub Find(ByVal sqlObj As SQLBuilder)
        sqlObj.SetSelect(Me.m_tableName, m_UidColumnName)
        SetCurrentQuery(sqlObj)
        Find()
    End Sub


    ''' <summary>
    ''' Populate the DOCollection with a subset of the search results
    ''' generated using the specified SQLBuilder.
    ''' </summary>
    ''' <param name="startIndex">
    ''' The index of the first row to be retrieved. Must be &gt;=0 and &lt;= the row count of the results.</param>
    ''' <param name="endIndex">
    ''' The index of the final row to be retrieved.</param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <para>This will retrieve the first 50 rows in the result set:</para>
    ''' <code>
    '''     Dim sColl as New StudentCollection
    '''     Dim mySql as SQLBuilder = sColl.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     sColl.FindRange(0, 49, mySql)
    ''' </code>
    ''' </example>
    Public Sub FindRange(ByVal startIndex As Integer, ByVal endIndex As Integer, ByVal sqlObj As SQLBuilder)
        SetCurrentQuery(sqlObj)
        Dim top As Integer = m_sqlObj.TopCount
        m_sqlObj.setTop(endIndex + 1)
        m_sqlObj.SetSelect(Me.m_tableName, m_UidColumnName)
        m_uidList = m_objDA.RunSQLReturnDataSet(m_sqlObj.RenderSelect())
        If top > 0 Then
            m_sqlObj.setTop(top)
        Else
            m_sqlObj.clearTop()
        End If
        For i As Integer = 0 To startIndex - 1
            m_uidList.Tables(0).Rows(i).Delete()
        Next
        m_uidList.Tables(0).AcceptChanges()
        m_active = True
    End Sub


    ''' <summary>
    ''' Performs a find which populates the collection and loads objects in to cache.
    ''' </summary>
    ''' <param name="sqlObj">Populated SQLBuilder</param>
    ''' <remarks>
    ''' Performing a find will FORCE load the actual DataObjects. 
    ''' This is a very costly method.
    ''' </remarks>
    <ObsoleteAttribute("The CurrentQuery should be set without passing in a QueryBuilder on the Find method.")> _
    Protected Sub ActiveFind(ByVal sqlObj As SQLBuilder)
        sqlObj.SetSelect(Me.m_tableName, m_UidColumnName)
        SetCurrentQuery(sqlObj)
        Find()
        If Count() > 0 Then
            Dim tmpObj As DataObject
            For i As Integer = 0 To m_uidList.Tables(0).Rows.Count - 1
                tmpObj = m_objFactory.Create(CType(m_uidList.Tables(0).Rows(i).Item(0), Guid))
                m_cache(i) = tmpObj
                tmpObj.Load()
            Next
        End If
    End Sub


    ''' <summary>
    ''' Performs a DELETE 
    ''' </summary>
    ''' <param name="sqlObj">Populated SQL Builder which has the delete limiter
    ''' </param>
    ''' <remarks>
    ''' Executing this command on a DOCollection will not alter it. It will retain whatever
    ''' presently exists in its collection and caches. To use a 'shadow collection' after
    ''' a Delete, use a call to the ActiveFind method to ensure the cache is populated 
    ''' with the complete collection.
    ''' </remarks>
    <ObsoleteAttribute("The CurrentQuery should be set without passing in a QueryBuilder on the Delete method.")> _
    Protected Sub Delete(ByVal sqlObj As SQLBuilder)
        If sqlObj.TableName = m_tableName Then
            m_objDA.RunSQL(sqlObj.RenderDelete())
        Else
            Throw New DOException("DOCollection: incompatible SQLBuilder Object")
        End If
    End Sub


    ''' <summary>
    ''' Performs a DELETE 
    ''' </summary>
    ''' <param name="sqlObj">Populated SQL Builder which has the delete limiter
    ''' </param>
    ''' <remarks>
    ''' Executing this command on a DOCollection will not alter it. It will retain whatever
    ''' presently exists in its collection and caches. To use a 'shadow collection' after
    ''' a Delete, use a call to the ActiveFind method to ensure the cache is populated 
    ''' with the complete collection.
    ''' </remarks>
    <ObsoleteAttribute("The CurrentQuery should be set without passing in a QueryBuilder on the Delete method.")> _
    Protected Sub Delete(ByVal sqlObj As SQLBuilder, ByVal t As DOTransaction)
        If sqlObj.TableName = m_tableName Then
            If t Is Nothing Then
                m_objDA.RunSQL(sqlObj.RenderDelete())
            Else
                t.AddSQL(sqlObj.RenderDelete())
            End If
        Else
            Throw New DOException("DOCollection: incompatible SQLBuilder Object")
        End If
    End Sub
#End Region

#Region "Current Methods"


    ''' <summary>
    ''' Get a new (empty) instance of a SQLBuilder for this DOCollection. 
    ''' </summary>
    ''' <returns>A correctly configured empty SQLBuilder for this DOCollection
    ''' </returns>
    ''' <remarks>
    ''' <para>The following is an example of GetSqlBuilder being used within a FindBy method of 
    ''' a StudentCollection:
    ''' </para>
    ''' <code>
    ''' Public Sub FindByStudentName(ByVal name As String)
    '''     Dim mySql As SQLBuilder = MyBase.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     MyBase.SetCurrentQuery(mySql)
    '''     MyBase.find()
    ''' End Sub
    ''' </code>
    ''' <para>GetSqlBuilder can also be called externally, for example:
    ''' </para>
    ''' <code>
    '''     Dim sColl as New StudentCollection
    '''     Dim mySql as SQLBuilder = sColl.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     sColl.SetCurrentQuery(mySql)
    '''     sColl.Find()
    ''' </code>
    ''' <para>In the above examples, the call to GetSqlBuilder() is the equivalent of calling:
    ''' </para>
    ''' <code>Dim mySQL as New SQLBuilder("Student")
    ''' </code>
    ''' <para>The GetSqlBuilder method differs from the GetCurrentQuery method in that 
    ''' GetSqlBuilder returns a new or empty instance
    ''' of a SQLBuilder, whereas GetCurrentQuery returns the SQLBuilder that has been previously set in 
    ''' the DOCollection using the SetCurrentQuery method or if none exists, it will provide a new one.
    ''' </para>
    ''' </remarks>
    Public Function GetSqlBuilder() As SQLBuilder
        Dim mySql As SQLBuilder = New SQLBuilder(m_tableName)
        Return mySql
    End Function



#Region "Find"

    ''' <summary>
    ''' Perform a Find to populate the DOCollection, using the SQLBuilder that has been 
    ''' previously set in the SetCurrentQuery() method.
    ''' </summary>
    ''' <remarks>
    ''' <para>Before calling Find(), SetCurrentQuery() must first be called to set 
    ''' the DOCollection's SQLBuilder. 
    ''' It is this previously-set SQLBuilder that will be used in Find().
    ''' </para>
    ''' </remarks>
    ''' <example>
    ''' <para>Find() is most commonly used where a SQLBuilder has been generated outside 
    ''' the DOCollection. For example:
    ''' </para>
    ''' <code>
    '''     Dim sColl as New StudentCollection
    '''     Dim mySql as SQLBuilder = sColl.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     sColl.SetCurrentQuery(mySql)
    '''     sColl.Find()
    ''' </code>
    ''' </example>
    Public Sub Find()
        If m_sqlObj Is Nothing Then Throw New DOException("No SqlBuilder defined")
        Me.m_sqlObj.StartRowNumber = -1
        Me.m_sqlObj.EndRowNumber = -1
        m_sqlObj.SetSelect(Me.m_tableName, m_UidColumnName)
        m_uidList = m_objDA.RunSQLReturnDataSet(m_sqlObj.RenderSelect())
        m_active = True
    End Sub

    ''' <overloads>
    ''' Populate the DOCollection with a subset of the search results
    ''' </overloads>
    ''' <summary>
    ''' Populate the DOCollection with a subset of the search results,
    ''' using the SQLBuilder that has been previously set in the SetCurrentQuery() method
    ''' </summary>
    ''' <param name="startIndex">The index of the first row to be retrieved. 
    ''' Must be &gt;=0 and &lt;= the row count of the results.</param>
    ''' <param name="endIndex">The index of the final row to be retrieved.</param>
    ''' <remarks>
    ''' <para>Before calling this method, SetCurrentQuery() must first be called to 
    ''' set the DOCollection's SQLBuilder. 
    ''' It is this previously-set SQLBuilder that will be used in FindRange().</para>
    ''' </remarks>
    ''' <example>
    ''' <para>This will retrieve the first
    ''' 50 rows in the result set:
    ''' </para>
    ''' <code>
    '''     Dim sColl as New StudentCollection
    '''     Dim mySql as SQLBuilder = sColl.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     sColl.SetCurrentQuery(mySql)
    '''     sColl.FindRange(0, 49)
    ''' </code>
    ''' </example>
    Public Sub FindRange(ByVal startIndex As Integer, ByVal endIndex As Integer)
        If m_sqlObj Is Nothing Then Throw New DOException("No SqlBuilder defined")
        Me.m_sqlObj.StartRowNumber = -1
        Me.m_sqlObj.EndRowNumber = -1
        Dim top As Integer = m_sqlObj.TopCount
        m_sqlObj.setTop(endIndex + 1)
        m_sqlObj.SetSelect(Me.m_tableName, m_UidColumnName)
        m_uidList = m_objDA.RunSQLReturnDataSet(m_sqlObj.RenderSelect())
        If top > 0 Then
            m_sqlObj.setTop(top)
        Else
            m_sqlObj.clearTop()
        End If
        For i As Integer = 0 To startIndex - 1
            m_uidList.Tables(0).Rows(i).Delete()
        Next
        m_uidList.Tables(0).AcceptChanges()
        m_active = True
    End Sub

#End Region


#Region "Fetch"

    ''' <summary>
    ''' Perform a Find to populate and prefetch the DOCollection, using the SQLBuilder that has been 
    ''' previously set in the SetCurrentQuery() method.
    ''' </summary>
    ''' <remarks>
    ''' <para>Before calling Fetch(), SetCurrentQuery() must first be called 
    ''' to set the DOCollection's SQLBuilder. 
    ''' It is this previously-set SQLBuilder that will be used in Fetch(). 
    ''' Fetch will optimize loading all the data in to objects upon invocation
    ''' </para>
    ''' </remarks>
    ''' <example>
    ''' <para>Fetch() is most commonly used where a SQLBuilder has been 
    ''' generated outside the DOCollection. For example:
    ''' </para>
    ''' <code>
    '''     Dim sColl as New StudentCollection
    '''     Dim mySql as SQLBuilder = sColl.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     sColl.SetCurrentQuery(mySql)
    '''     sColl.Fetch()
    ''' </code>
    ''' </example>
    Public Sub Fetch()
        If m_sqlObj Is Nothing Then Throw New DOException("No SqlBuilder defined")
        Me.m_sqlObj.StartRowNumber = -1
        Me.m_sqlObj.EndRowNumber = -1
        Dim obj As DataObject = m_objFactory.Create()
        m_sqlObj.SetSelect(Me.m_tableName, "*") 'All column names
        m_resultSet = m_objDA.RunSQLReturnDataSet(m_sqlObj.RenderSelect())
        If Not m_resultSet Is Nothing Then
            For i As Integer = 0 To m_resultSet.Tables(0).Rows.Count - 1
                obj = m_objFactory.Create(Me, i)
                m_cache(i) = obj
            Next
        End If
        m_active = True
    End Sub

    ''' <summary>
    ''' Populate the DOCollection with a subset of the search results
    ''' generated using the set query.
    ''' </summary>
    ''' <param name="startIndex">The index of the first row to be retrieved. 
    ''' Must be &gt;=0 and &lt;= the row count of the results.
    ''' </param>
    ''' <param name="endIndex">The index of the final row to be retrieved.
    ''' </param>
    ''' <remarks>
    ''' </remarks>
    ''' <example>
    ''' <para>This will retrieve the first 50 rows in the result set:
    ''' </para>
    ''' <code>
    '''     Dim sColl as New StudentCollection
    '''     Dim mySql as SQLBuilder = sColl.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     sColl.FindRange(0, 49)
    ''' </code>
    ''' </example>
    Public Sub FetchRange(ByVal startIndex As Integer, ByVal endIndex As Integer)
        If m_sqlObj Is Nothing Then Throw New DOException("No SqlBuilder defined")
        Me.m_sqlObj.StartRowNumber = -1
        Me.m_sqlObj.EndRowNumber = -1
        Dim obj As DataObject = m_objFactory.Create()
        Dim top As Integer = m_sqlObj.TopCount
        m_sqlObj.setTop(endIndex + 1)
        m_sqlObj.SetSelect(Me.m_tableName, "*") 'All column names
        m_resultSet = m_objDA.RunSQLReturnDataSet(m_sqlObj.RenderSelect())
        If top > 0 Then
            m_sqlObj.setTop(top)
        Else
            m_sqlObj.clearTop()
        End If
        For i As Integer = 0 To startIndex - 1
            m_resultSet.Tables(0).Rows(i).Delete()
        Next
        m_resultSet.Tables(0).AcceptChanges()
        If Not m_resultSet Is Nothing Then
            For i As Integer = 0 To m_resultSet.Tables(0).Rows.Count - 1
                obj = m_objFactory.Create(Me, i)
                m_cache(i) = obj
            Next
        End If
        m_active = True
    End Sub



    ''' <summary>
    ''' Fetches only the selected columns in the query, returning
    ''' all matching rows. This allows you to retrieve partially loaded DataObject, instead
    ''' of retrieving all columns in the object.
    ''' </summary>
    ''' <remarks>
    ''' This is a more complex method than Fetch() or Find() and requires careful usage.
    ''' </remarks>
    Public Sub FetchSelected()
        FetchSelected(-1, -1)
    End Sub

    ''' <summary>
    ''' Fetches only the selected columns in the query, returning
    ''' matching rows between the specified index. This allows you to retrieve partially loaded DataObject, instead
    ''' of retrieving all columns in the object.
    ''' </summary>
    ''' <param name="startIndex">Zero-based index.</param>
    ''' <param name="endIndex">Zero-based index.</param>
    ''' <remarks>
    ''' This is a new advanced method which currently requires SQL Server 2005, and careful usage.
    ''' TODO: This should be refactored into a generic method which can be implemented for different 
    ''' database types
    ''' </remarks>
    Public Sub FetchSelected(ByVal startIndex As Integer, ByVal endIndex As Integer)
        If m_sqlObj Is Nothing Then Throw New DOException("No SqlBuilder defined")
        Me.m_sqlObj.StartRowNumber = startIndex + 1
        Me.m_sqlObj.EndRowNumber = endIndex + 1
        If m_sqlObj.Selections.Length = 0 Then
            m_sqlObj.SetSelect(Me.m_tableName, "*")
        End If
        m_resultSet = m_objDA.RunSQLReturnDataSet(m_sqlObj.RenderSelect())
        If Not m_resultSet Is Nothing Then
            For i As Integer = 0 To m_resultSet.Tables(0).Rows.Count - 1
                Dim obj As DataObject = m_objFactory.Create(m_resultSet.Tables(0).Rows(i), m_sqlObj.Selections())
                m_cache(i) = obj
            Next
        End If
        m_active = True
    End Sub

#End Region

    ''' <summary>
    ''' Perform a DELETE using the set SQLBuilder which filters the collection
    ''' </summary>
    ''' <remarks>
    ''' Requires call to SetSQLBuilder()
    ''' </remarks>
    Public Sub Delete()
        If m_sqlObj Is Nothing Then Throw New DOException("No SqlBuilder defined")
        m_sqlObj.SetSelect(Me.m_tableName, m_UidColumnName)
        m_objDA.RunSQL(m_sqlObj.RenderDelete())
    End Sub


    ''' <summary>
    ''' Perform a DELETE using the set SQLBuilder which filters the collection
    ''' </summary>
    ''' <param name="t">Valid DOTransaction
    ''' </param>
    ''' <remarks>
    ''' Requires call to SetSQLBuilder()
    ''' </remarks>
    Public Sub Delete(ByVal t As DOTransaction)
        If m_sqlObj Is Nothing Then Throw New DOException("No SqlBuilder defined")
        m_sqlObj.SetSelect(Me.m_tableName, m_UidColumnName)
        If t Is Nothing Then
            m_objDA.RunSQL(m_sqlObj.RenderDelete())
        Else
            t.AddSQL(m_sqlObj.RenderDelete())
        End If
    End Sub


    ''' <summary>
    ''' Return a new (empty) instance of a SearchBuilder.
    ''' </summary>
    ''' <returns>An instance of a SearchBuilder</returns>
    ''' <remarks>
    ''' <para>The SearchBuilder returned is not specific to the DataObject type used by the DOCollection. 
    ''' That is, calling this function is the equivalent of the simple statement:
    ''' </para>
    ''' <code>Dim sb as New SearchBuilder
    ''' </code>
    ''' </remarks>
    Public Function GetSearchBuilder() As SearchBuilder
        Dim sb As New SearchBuilder
        Return sb
    End Function


    ''' <summary>
    ''' Get the current SQLBuilder for this collection.
    ''' </summary>
    ''' <returns>The SQLBuilder instance that is currently set in the DOCollection, 
    ''' and which is used within the Find(), FindRange() and Count() methods of the DOCollection.</returns>
    ''' <remarks>
    ''' <para>GetCurrentQuery() is used in conjunction with the SetCurrentQuery() method, 
    ''' retrieving the SQLBuilder that was previously
    ''' set with SetCurrentQuery(). If no SQLBuilder has been set, it returns a new 
    ''' (empty) instance of the relevant SQLBuilder for the DOCollection.
    ''' </para>
    ''' <para>GetCurrentQuery differs from the GetSqlBuilder method in that the 
    ''' latter only returns a new (empty) instance of a SQLBuilder.
    ''' </para>     
    ''' </remarks>
    Public Function GetCurrentQuery() As SQLBuilder
        If m_sqlObj Is Nothing Then
            Dim mySql As SQLBuilder = New SQLBuilder(m_tableName)
            m_sqlObj = mySql
        End If
        Return m_sqlObj
    End Function


    ''' <summary>
    ''' Set the current SQLBuilder for this collection
    ''' </summary>
    ''' <param name="sql">The SQLBuilder object to be used in the next Find(), FindRange() or Count() call on the DOCollection.</param>
    ''' <remarks>
    ''' <para>SetCurrentQuery is used to set the SQLBuilder that will be used in the next
    ''' call to Find(), FindRange() or Count() on the DOCollection.
    ''' </para>
    ''' <para>It is most commonly used where a SQLBuilder has been generated outside the 
    ''' DOCollection. For example:
    ''' </para>
    ''' <code>
    '''     Dim sColl as New StudentCollection
    '''     Dim mySql as SQLBuilder = sColl.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     sColl.SetCurrentQuery(mySql)
    '''     sColl.Find()
    ''' </code>
    ''' <para>Using the SetCurrentQuery() method in conjunction with Count() and FindRange() 
    ''' allows increases in efficiency and performance, such as in implementing custom
    ''' paging for ASP.NET Datagrids.
    ''' </para>
    ''' <para>For example, to obtain the row count of a collection, without retrieving all the data:</para>
    ''' <code>
    '''     Dim sColl as New StudentCollection
    '''     Dim mySql as SQLBuilder = sColl.GetSqlBuilder()
    '''     mySql.addWhere(Student.Columns.Name, name)
    '''     sColl.SetCurrentQuery(mySql)
    '''     Dim rowCount as Integer=sColl.Count()  
    ''' </code>
    ''' <para>A specific range of rows in the collection could then be retrieved:
    ''' </para>
    ''' <code>
    '''     If rowCount >= 50 Then
    '''         sColl.FindRange(0,50)
    '''         For i as Integer=0 to 50
    '''             Dim sObj as Student=sColl(i)
    '''             
    '''             'Perform an action on sObj
    '''         Next
    '''     End If     
    ''' </code>
    ''' </remarks>
    Public Sub SetCurrentQuery(ByVal sql As SQLBuilder)
        If sql.TableName = m_tableName Then
            'throw away old cache
            If Not m_cache Is Nothing Then
                Try
                    m_cache.Clear()
                Catch ex As Exception
                End Try
            End If
            m_cache = New Hashtable
            If Not m_uidList Is Nothing Then
                Try
                    m_uidList.Dispose()
                Catch ex As Exception
                End Try
            End If
            m_uidList = Nothing
            m_active = False
            m_sqlObj = sql
        Else
            Throw New DOException("DOCollection: incompatible SQLBuilder Object")
        End If
    End Sub


    ''' <summary>
    ''' Clears the current SQLBuilder, and replaces it with a fresh SQLBuilder.
    ''' </summary>
    ''' <remarks>
    ''' This method is most useful if you are continually modifying the DOCollection's
    ''' internal SQLBuilder, such as during a loop.
    ''' </remarks>
    Public Sub ResetCurrentQuery()
        'throw away old cache
        If Not m_cache Is Nothing Then
            Try
                m_cache.Clear()
            Catch ex As Exception
            End Try
        End If
        m_cache = New Hashtable
        If Not m_uidList Is Nothing Then
            Try
                m_uidList.Dispose()
            Catch ex As Exception
            End Try
        End If
        m_uidList = Nothing
        Dim mySql As SQLBuilder = New SQLBuilder(m_objFactory.Create().TableName())
        m_sqlObj = mySql
    End Sub


    ''' <summary>
    ''' Copy all dataobjects to an array of DOTOs. The type passed in will 
    ''' be the type of DOTO which is created and returned.
    ''' </summary>
    ''' <param name="doType">The type of DOTO to be populated and returned.</param>
    ''' <returns>Array of DOTO's (DataObject Transfer Objects)</returns>
    ''' <remarks>
    ''' This call will load each data object in to memory and create a matching transfer object.
    ''' Can be a heavy processing load.
    ''' </remarks>
    Public Function ToArray(ByVal doType As Type) As Object()
        Dim al As New ArrayList
        For i As Integer = 0 To Me.Count - 1
            al.Add(Me(i).GetTO(doType))
        Next
        Return al.ToArray(doType)
    End Function


    ''' <summary>
    ''' Get the number of elements in the collection.
    ''' </summary>
    ''' <value>Number of elements in the collection.</value>
    ''' <remarks>
    ''' If the collection has not been initialised through a 'find' call then
    ''' the count will be 0. There is no differentiation between unitialised and 
    ''' initalised with an empty result set.
    ''' </remarks>
    Public ReadOnly Property Count() As Integer
        Get
            If m_active = True Then
                If Not m_uidList Is Nothing Then Return m_uidList.Tables(0).Rows.Count
                If Not m_resultSet Is Nothing Then Return m_resultSet.Tables(0).Rows.Count
            Else
                If Not m_sqlObj Is Nothing Then
                    Const label As String = "myCount"
                    Dim ds As DataSet = m_objDA.RunSQLReturnDataSet(m_sqlObj.RenderCount(label, Me.m_tableName, Me.m_UidColumnName))
                    If Not TypeOf (ds.Tables(0).Rows(0)(label)) Is DBNull Then
                        Dim tmpCount As Integer = ds.Tables(0).Rows(0)(label)
                        ds.Dispose()
                        Return tmpCount
                    End If
                Else
                    Return 0
                End If
            End If
            Return 0
        End Get
    End Property



    ''' <summary>
    ''' Gets the sum of the values in the specified column in the collection, using the current query as a base.
    ''' </summary>
    ''' <remarks>
    ''' </remarks>
    Public Function Sum(ByVal table As DataObject, ByVal column As [Enum], ByVal columnType As DBColumnType) As Double
        If Not m_sqlObj Is Nothing Then
            Const label As String = "mySum"
            Dim ds As DataSet = m_objDA.RunSQLReturnDataSet(m_sqlObj.RenderSum(table, column, label, columnType))
            If Not TypeOf (ds.Tables(0).Rows(0)(label)) Is DBNull Then
                Dim tmpSum As Double = ds.Tables(0).Rows(0)(label)
                ds.Dispose()
                Return tmpSum
            End If
        Else
            Return 0
        End If
    End Function


    ''' <summary>
    ''' Gets the average of the values in the specified column in the collection, using 
    ''' the current query as a base.
    ''' </summary>
    Public Function Average(ByVal table As DataObject, ByVal column As [Enum], ByVal columnType As DBColumnType) As Double
        If Not m_sqlObj Is Nothing Then
            Const label As String = "myAvg"
            Dim ds As DataSet = m_objDA.RunSQLReturnDataSet(m_sqlObj.RenderAverage(table, column, label, columnType))
            If Not TypeOf (ds.Tables(0).Rows(0)(label)) Is DBNull Then
                Dim tmpAvg As Double = ds.Tables(0).Rows(0)(label)
                ds.Dispose()
                Return tmpAvg
            End If
        Else
            Return 0
        End If
    End Function



    ''' <summary>
    ''' Retrieve the DataObject from the collection using the specified index.
    ''' </summary>
    ''' <param name="i">Index in to the collection</param>
    ''' <value></value>
    ''' <remarks>
    ''' If the index is out of bounds (see Count()) this will throw an IndexOutOfBoundsException.
    ''' 
    ''' This technique will therefore only READ from the database the first time the cache is empty
    ''' thus, if the underlying data changes, there is no implied way of updating the cache other than
    ''' refinding.
    ''' 
    ''' This will retrieve the indexed data object to a local cached item. Subsequent reads do not 
    ''' access the database.
    ''' </remarks>
    Default Public ReadOnly Property Item(ByVal i As Integer) As DataObject
        Get
            'If Find() or Fetch() has not been called,
            'do an implicit Find() as a fallback
            If m_uidList Is Nothing Then
                Me.Find()
            End If
            Try
                Dim tmpObj As DataObject
                tmpObj = m_cache.Item(i)
                If tmpObj Is Nothing Then
                    If i > m_uidList.Tables(0).Rows.Count - 1 Then
                        Throw New IndexOutOfRangeException("DOCollection: Query DataSet Out of Bounds. Attempted index:" & i)
                    End If
                    tmpObj = m_objFactory.Create(CType(m_uidList.Tables(0).Rows(i).Item(0), Guid))
                    m_cache(i) = tmpObj
                    tmpObj.Load()
                End If
                Return tmpObj
            Catch nex As NullReferenceException
                Throw New DOException("Collection not populated. You need to call find() to populate the collection.")
            End Try
        End Get
    End Property


    ''' <summary>
    ''' Get the value of the column specified in the index.
    ''' </summary>
    ''' <param name="i">Index in to the collection
    ''' </param>
    ''' <param name="valuePropertyName">Using the columns enum from the corresponding DataObject</param>
    ''' <returns></returns>
    ''' <remarks>
    ''' </remarks>
    Public Function GetValueByName(ByVal i As Integer, ByVal valuePropertyName As [Enum]) As Object
        Return CType(m_objFactory.getDOType().InvokeMember(valuePropertyName.ToString(), BindingFlags.NonPublic Or BindingFlags.GetProperty Or BindingFlags.Public Or BindingFlags.Instance, Nothing, Item(i), New Object() {}), Object)
    End Function


    ''' <summary>
    ''' Explicit refresh of data in the cache. Any request for a dataObject will
    ''' force a new database query.
    ''' </summary>
    ''' <remarks>
    ''' </remarks>
    Public Sub FlushLocalCache()
        m_cache = New Hashtable
    End Sub


    ''' <summary>
    ''' Populate (lazy load) the collection by the finding all entries in a table
    ''' which matches the subclassed DataObject.
    ''' </summary>
    ''' <remarks>
    ''' </remarks>
    Public Sub FindAll()
        Me.SetCurrentQuery(Me.GetSqlBuilder())
    End Sub


    ''' <summary>
    ''' Populate and prefetch the collection by the finding all entries in a table
    ''' which matches the subclassed DataObject.
    ''' </summary>
    ''' <remarks>
    ''' </remarks>
    Public Sub FetchAll()
        Me.SetCurrentQuery(Me.GetSqlBuilder())
        Fetch()
    End Sub



    ''' <summary>
    ''' Populate the collection by the finding a record with the specified primary key value.
    ''' </summary>
    ''' <param name="uid">Primary key to find</param>
    ''' <remarks>
    ''' </remarks>
    Public Sub FindByPK(ByVal uid As Guid)
        Me.ResetCurrentQuery()
        Dim mySql As SQLBuilder = Me.GetSqlBuilder()
        mySql.addWhere(m_objFactory.Create().UidColumn(), uid.ToString())
        Me.SetCurrentQuery(mySql)
        Fetch()
    End Sub



    ''' <summary>
    ''' Check if the primary key value exists in the database.
    ''' </summary>
    ''' <param name="uid">Primary key to find</param>
    ''' <returns>True if the PK exists in the database.</returns>
    ''' <remarks>
    ''' </remarks>
    Public Function PKExists(ByVal uid As Guid) As Boolean
        Try
            FindByPK(uid)
            If Count() < 1 Then
                Return False
            End If
            Return True
        Catch ex As Exception
            Return False
        End Try
    End Function



    ''' <summary>
    ''' Return the index of the data object in the collection.
    ''' </summary>
    ''' <param name="uid"></param>
    ''' <returns>Returns the location of the data object if it is found in the 
    ''' collection, -1 if it is not.
    ''' </returns>
    ''' <remarks>
    ''' </remarks>
    Public Function IndexOf(ByVal uid As Guid) As Integer
        If Active() Then
            For i As Integer = 0 To Count - 1
                If Item(i).Uid().Equals(uid) Then
                    Return i
                End If
            Next
        End If
        Return -1
    End Function



    ''' <summary>
    ''' Return the index of the data object in the collection.
    ''' </summary>
    ''' <param name="valuePropertyName"></param>
    ''' <param name="value"></param>
    ''' <returns>Returns the location of the data object if it is found in the collection, 
    ''' -1 if it is not.
    ''' </returns>
    ''' <remarks>
    ''' </remarks>
    Public Function IndexOf(ByVal valuePropertyName As [Enum], ByVal value As String) As Integer
        Dim i As Integer = 0
        Dim str As String
        If Active() Then
            For i = 0 To Count() - 1
                str = CType(m_objFactory.getDOType().InvokeMember(valuePropertyName.ToString(), BindingFlags.NonPublic Or BindingFlags.GetProperty Or BindingFlags.Public Or BindingFlags.Instance, Nothing, Item(i), New Object() {}), Object).ToString()
                If str = value Then
                    Return i
                End If
            Next
        End If
        Return -1
    End Function


    ''' <summary>
    ''' Find the next Item in the DOCollection after the Item with the specified UID.
    ''' </summary>
    ''' <param name="currentId">The UID of the current Item</param>
    ''' <param name="foundCurrent">Returns true if the current Item has been found in the Collection.
    ''' This is designed for dynamic collections, where Items may be removed.</param>
    ''' <param name="atEnd">Returns true if the current Item is the last Item in the Collection.</param>
    ''' <returns>If the Item with UID currentId is not found in the DOCollection, or if it 
    ''' is the last Object in the DOCollection, the method returns a null value. 
    ''' Otherwise, it returns the next DataObject in the Collection.
    ''' </returns>
    ''' <remarks>
    ''' <para>This method is used in conjunction with GetPrevious for navigating DOCollections.</para>
    ''' <example>
    ''' Dim sColl as New StudentCollection
    ''' sColl.FindAll()
    ''' If sColl.Count &gt; 0 Then
    '''     Dim currentStudent As Student = sColl(0)
    '''     Dim foundCurrent As Boolean = False
    '''     Dim atEnd As Boolean = False
    '''     Dim nextStudent As Student = sColl.GetNext(currentStudent, foundCurrent, atEnd)
    ''' End If
    ''' </example>
    ''' </remarks>
    Public Function GetNext(ByVal currentId As Guid, ByRef foundCurrent As Boolean, ByRef atEnd As Boolean) As DataObject
        If Not Me.Active() Then
            Me.Find()
        End If
        foundCurrent = False
        Dim currentIndex As Integer = Me.IndexOf(currentId)
        If currentIndex = -1 Then
            Return Nothing
        Else
            foundCurrent = True
            If currentIndex = Me.Count - 1 Then
                atEnd = True
                Return Nothing
            End If
            Dim aObj As DataObject = Me.Item(currentIndex + 1)
            Return aObj
        End If
    End Function



    ''' <summary>
    ''' Find the Item in the DOCollection that precedes the Item with the specified UID.
    ''' </summary>
    ''' <param name="currentId">The UID of the current Item</param>
    ''' <param name="foundCurrent">Returns true if the current Item has been found in the Collection.
    ''' This is designed for dynamic collections, where Items may be removed.</param>
    ''' <param name="atStart">Returns true if the current Item is the first Item in the Collection.</param>
    ''' <returns>If the Item with UID currentId is not found in the DOCollection, or if it is the first Object in the DOCollection, the method returns a null value. Otherwise, it returns the previous DataObject in the Collection.</returns>
    ''' <remarks>
    ''' <para>This method is used in conjunction with GetNext for navigating DOCollections.</para>
    ''' <example>
    ''' Dim sColl as New StudentCollection
    ''' sColl.FindAll()
    ''' If sColl.Count &gt; 0 Then
    '''     Dim currentStudent As Student = sColl(SColl.Count() - 1)
    '''     Dim foundCurrent As Boolean = False
    '''     Dim atStart As Boolean = False
    '''     Dim previousStudent As Student = sColl.GetPrevious(currentStudent, foundCurrent, atStart)
    ''' End If
    ''' </example>
    ''' </remarks>
    Public Function GetPrevious(ByVal currentId As Guid, ByRef foundCurrent As Boolean, ByRef atStart As Boolean) As DataObject
        If Not Me.Active Then
            Me.Find()
        End If
        foundCurrent = False
        Dim currentIndex As Integer = Me.IndexOf(currentId)
        If currentIndex = -1 Then
            Return Nothing
        Else
            foundCurrent = True
            If currentIndex = 0 Then
                atStart = True
                Return Nothing
            End If
            Dim aObj As DataObject = Me.Item(currentIndex - 1)
            Return aObj
        End If
    End Function



    ''' <summary>
    ''' Create a datasource
    ''' </summary>
    ''' <param name="IncludeUid">Include the primary key column</param>
    ''' <param name="valuePropertyNames">Param array of column references (using the columns enum 
    ''' from the corresponding DataObject)
    ''' </param>
    ''' <returns>A DataView of items (as an iCollection)</returns>
    ''' <remarks>
    ''' </remarks>
    Public Function CreateDataSource(ByVal IncludeUid As Boolean, ByVal ParamArray valuePropertyNames() As [Enum]) As ICollection
        Return CreateDataView(IncludeUid, valuePropertyNames)
    End Function



    ''' <summary>
    ''' Create a datasource
    ''' </summary>
    ''' <param name="IncludeUid">Include the primary key column</param>
    ''' <param name="valuePropertyNames">Param array of column references (using the columns 
    ''' enum from the corresponding DataObject)</param>
    ''' <returns>A DataView of items (as a DataView)</returns>
    ''' <remarks>
    ''' </remarks>
    Public Function CreateDataView(ByVal IncludeUid As Boolean, ByVal ParamArray valuePropertyNames() As [Enum]) As DataView
        Dim dt As New DataTable
        Dim dr As DataRow
        Dim uidColName As String = GetUidColumnName()
        If IncludeUid Then dt.Columns.Add(New DataColumn(uidColName, GetType(System.Guid)))
        Dim i, j, k As Integer
        For i = 0 To valuePropertyNames.Length - 1
            Dim pi As PropertyInfo = m_objFactory.getDOType().GetProperty(valuePropertyNames(i).ToString())
            dt.Columns.Add(New DataColumn(valuePropertyNames(i).ToString(), pi.PropertyType))
        Next

        For i = 0 To Count() - 1
            dr = dt.NewRow()
            k = 0
            If IncludeUid Then
                dr(k) = m_objFactory.getDOType().InvokeMember(uidColName, BindingFlags.NonPublic Or BindingFlags.GetProperty Or BindingFlags.Public Or BindingFlags.Instance, Nothing, Item(i), New Object() {})
                k += 1
            End If
            For j = 0 To valuePropertyNames.Length - 1
                dr(k) = m_objFactory.getDOType().InvokeMember(valuePropertyNames(j).ToString(), BindingFlags.NonPublic Or BindingFlags.GetProperty Or BindingFlags.Public Or BindingFlags.Instance, Nothing, Item(i), New Object() {})
                k += 1
            Next
            dt.Rows.Add(dr)
        Next

        Dim dv As New DataView(dt)
        Return dv
    End Function


    ''' <summary>
    ''' Creates a bindable datasource for a generic web control
    ''' </summary>
    ''' <param name="valuePropertyName"></param>
    ''' <param name="emptyLine"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function CreateControlDataSource(ByVal valuePropertyName As [Enum], Optional ByVal emptyLine As String = "") As ICollection
        '  If Count = 0 Then Return Nothing
        Dim dt As New DataTable
        Dim dr As DataRow
        dt.Columns.Add(New DataColumn(GetUidColumnName(), GetType(String)))
        dt.Columns.Add(New DataColumn(valuePropertyName.ToString(), GetType(String)))

        If Not emptyLine = "" Then
            dr = dt.NewRow()
            dr(0) = "-1"
            dr(1) = emptyLine
            dt.Rows.Add(dr)
        End If

        Dim i As Integer
        For i = 0 To Count() - 1
            dr = dt.NewRow()
            dr(0) = Item(i).Uid.ToString()
            dr(1) = CType(m_objFactory.getDOType().InvokeMember(valuePropertyName.ToString(), BindingFlags.NonPublic Or BindingFlags.GetProperty Or BindingFlags.Public Or BindingFlags.Instance, Nothing, Item(i), New Object() {}), Object).ToString
            dt.Rows.Add(dr)
        Next i

        Dim dv As New DataView(dt)
        Return dv
    End Function



    ''' <summary>
    ''' Bind a UI control to this DOCollection as a Data Source
    ''' Empty line will provide a message as the first item in the bound list - 
    ''' validation code should handle a value of -1 the the combo is on this element.
    ''' </summary>
    ''' <param name="valuePropertyName">Column reference (using the columns enum from the 
    ''' corresponding DataObject) of the Value on the control.</param>
    ''' <param name="boundWebControl">Web control to bind to</param>
    ''' <param name="emptyLine">The message to include on the first line</param>
    ''' <remarks>
    ''' The message on the first line is a -1 value.
    ''' </remarks>
    Public Sub BindDataSource(ByVal valuePropertyName As [Enum], ByVal boundWebControl As System.Web.UI.WebControls.ListControl, ByVal emptyLine As String)
        boundWebControl.DataSource = Me.CreateControlDataSource(valuePropertyName, emptyLine)
        boundWebControl.DataTextField = valuePropertyName.ToString()
        boundWebControl.DataValueField = Me.GetUidColumnName()
        boundWebControl.DataBind()
    End Sub


    ''' <summary>
    ''' Bind a UI control to this DOCollection as a Data Source
    ''' Empty line will provide a message as the first item in the bound list - 
    ''' validation code should handle a value of -1 the the combo is on this element.
    ''' </summary>
    ''' <param name="valuePropertyName">Column reference (using the columns enum from the 
    ''' corresponding DataObject) of the Value on the control.</param>
    ''' <param name="boundWebControl">Web control to bind to</param>
    ''' <remarks>
    ''' The message on the first line is a -1 value.
    ''' </remarks>
    Public Sub BindDataSource(ByVal valuePropertyName As [Enum], ByVal boundWebControl As System.Web.UI.WebControls.ListControl)
        boundWebControl.DataSource = Me.CreateControlDataSource(valuePropertyName, "")
        boundWebControl.DataTextField = valuePropertyName.ToString()
        boundWebControl.DataValueField = Me.GetUidColumnName()
        boundWebControl.DataBind()
    End Sub




    ''' <summary>
    ''' Bind a UI control to this DOCollection as a Data Source
    ''' Empty line will provide a message as the first item in the bound list - 
    ''' validation code should handle a value of -1 the the combo is on this element.
    ''' </summary>
    ''' <param name="valuePropertyName">Column reference (using the columns enum from 
    ''' the corresponding DataObject) of the Value on the control.</param>
    ''' <param name="boundControl">Control to bind to</param>
    ''' <param name="emptyLine">The message to include on the first line</param>
    ''' <remarks>
    ''' The message on the first line is a -1 value.
    ''' </remarks>
    Public Sub BindDataSource(ByVal valuePropertyName As [Enum], ByVal boundControl As System.Windows.Forms.ListControl, ByVal emptyLine As String)
        boundControl.DataSource = Me.CreateControlDataSource(valuePropertyName, emptyLine)
        boundControl.DisplayMember = valuePropertyName.ToString()
        boundControl.ValueMember = Me.GetUidColumnName()
    End Sub


    ''' <summary>
    ''' Bind a UI control to this DOCollection as a Data Source
    ''' Empty line will provide a message as the first item in the bound list - 
    ''' validation code should handle a value of -1 the the combo is on this element.
    ''' </summary>
    ''' <param name="valuePropertyName">Column reference (using the columns enum 
    ''' from the corresponding DataObject) of the Value on the control.</param>
    ''' <param name="boundControl">Control to bind to</param>
    ''' <remarks>
    ''' The message on the first line is a -1 value.
    ''' </remarks>
    Public Sub BindDataSource(ByVal valuePropertyName As [Enum], ByVal boundControl As System.Windows.Forms.ListControl)
        boundControl.DataSource = Me.CreateControlDataSource(valuePropertyName, "")
        boundControl.DisplayMember = valuePropertyName.ToString()
        boundControl.ValueMember = Me.GetUidColumnName()
    End Sub


    ''' <summary>
    ''' Get the name of the Primary Key column.
    ''' </summary>
    ''' <returns>Name of the primary key column</returns>
    ''' <remarks>
    ''' </remarks>
    Public Function GetUidColumnName() As String
        Return m_objFactory.Create().UIDColumnName
    End Function

    Public Sub Dispose() Implements System.IDisposable.Dispose
        Try
            If Not Me.m_cache Is Nothing Then
                Me.m_cache.Clear()
            End If
        Catch ex As Exception
        End Try
        Try
            If Not Me.m_uidList Is Nothing Then
                Me.m_uidList.Dispose()
            End If
        Catch ex As Exception
        End Try
    End Sub

    Protected Overrides Sub Finalize()
        Me.Dispose()
        MyBase.Finalize()
    End Sub

    Public ReadOnly Property ActiveDataSet() As DataSet
        Get
            If m_resultSet Is Nothing Then
                Throw New DOException("Collection not populated correctly. You need to call Fetch() to populate the collection correctly.")
            End If
            Return m_resultSet
        End Get
    End Property
#End Region


#Region "IEnumerable"

    Public Function GetEnumerator() As System.Collections.IEnumerator Implements System.Collections.IEnumerable.GetEnumerator
        If Not Me.m_active Then Throw New DOException("Cannot get enumerate an inactive DOCollection")
        Return New DOCollectionEnumerator(Me)
    End Function

#End Region








End Class
